Visual Database Tools

Visual Basic 5 Enterprise Edition was the first language version that integrated a suite of tools for working with databases from within the IDE. Before that, you had to switch to an external program, such as Access, SQL Server Enterprise Manager, or FoxPro, whenever you needed to create or edit a table, set a relationship between two tables, design a query, and so on. The Visual Database Tools suite has been inherited by Visual Basic 6, and most of its tools are also available in the Professional Edition. The new version of Visual Database Tools is better integrated in the environment, and in fact some menus in the IDE—most notably the Query menu, the Diagram menu, and some commands in the File, Edit, and View menus—become active only when a window of Visual Database Tools has the focus.

In this section, I review a few of these tools and show how you can use them to manage your databases. Remember that the Database Designer window and the Query Designer window are available only with Visual Basic 6 Enterprise Edition.

The entry point for using Visual Database Tools is the DataView window, which you can display using the corresponding command in the View menu or by clicking on its yellow icon in the standard toolbar. This window is a repository for all the database connections you want to have available at all times; these connections are called data links. For example, the DataView window shown in Figure 8-4 contains data links to the Biblio and NWind Jet databases and two connections to the Pubs SQL Server database, one of which connects through the native OLE DB provider for SQL Server while the other connects through the MSDASQL provider for ODBC sources. You can open these data link nodes and display all the tables, views, diagrams, and stored procedures in the database. (In an Oracle database, you'll see two more folders, Functions and Synonyms.) You can expand a table or a view node to see the individual fields that make up that particular table or view, and you can look at an object's properties by right-clicking on it and selecting the Properties menu command. Let me summarize what you can do with the DataView window:

• You can create new data links and add them to the DataView window by clicking on the rightmost icon on the DataView window's toolbar. You can also delete an existing data link—or any other object displayed in the window—by clicking on it and pressing the Del key or by right-clicking on it and selecting the Delete command from the pop-up menu. Data links are stored in the system Registry and aren't associated with any particular Visual Basic project.
• You can see the contents of a table or a view (as shown in Figure 8-5) by double-clicking on it or by selecting the Open command from the pop-up menu. If you have the right permissions, you can even edit the values displayed in the grid and save your changes to the underlying database.
• You can create a new database table by right-clicking on the Tables folder and selecting the New Table menu command. In the same menu, you'll find other commands for showing or hiding system tables or for filtering tables according to their owner. Keep in mind that these capabilities aren't supported for all databases. For example, the SQL Server provider supports table creation whereas the Jet provider doesn't.
• You can change the layout of an existing table by right-clicking on it and selecting the Design menu command. Again, this feature isn't supported by the Jet providers in version 3.51 and 4.0. Interestingly, if you change the type of columns of a table or if you add or delete columns, the DataView window automatically moves existing data into the new layout.
• You can right-click on any object and select the Properties menu command to browse its attributes. This is especially useful for revising the values used for the connection, the owner of each table, and the type of columns. (See Figure 8-4.)
• You can create new stored procedures or edit existing ones. A stored procedure is a sequence of database commands that's stored in the database in a precompiled and optimized form. A stored procedure can (and often does) accept parameters and return a set of records. Stored procedures can be edited in the SQL Editor, another member of the Visual Database Tool suite. (See Chapter 14 for a description of the SQL Editor.)
• You can add a new trigger to an existing table by selecting the New Trigger command from the pop-up menu. Triggers are special stored procedures that are automatically executed whenever a record in the table is modified, inserted, or deleted.
• You can create a new database diagram or edit an existing one by selecting the corresponding command from the menu that pops up when you right-click the Database Diagram folder or on an existing database diagram object. Database diagrams are graphical views of part or all the tables in the database; they display all the relationships between these objects, and you can annotate these diagrams with your own comments.

Figure 8-4. Objects displayed in the DataView window can display a Properties dialog box.

Figure 8-5. The TitleView window, as seen in Visual Database Tools.

A few of these operations are so important that they deserve a more detailed description.

Before working on a database with the DataView window, you must establish a data link to that database. A data link includes several pieces of information, including the name of the OLE DB provider used to connect to the database engine, the name of the particular database you want to access, and other login data such as the user name and password.

Figure 8-6. To create a new data link, you must select an OLE DB provider.

You can create a new data link by clicking on the rightmost icon in the DataView toolbar or by selecting the Add A Data Link command from the pop-up menu that appears when you right-click on the window. This command starts a four-step wizard, whose first page is visible in Figure 8-6. In this page, you select the OLE DB provider you want to use to connect to the database. By default, ADO uses the Microsoft OLE DB Provider for ODBC Drivers (MSDASQL), which lets you connect to virtually any relational and ISAM database in the world. For some data sources, you can get better performance and more features using providers specifically created for those particular data sources.

The content of the second page of the wizard depends on which provider you selected in the opening tab. For example, when connecting to a Jet database you have to select only the path of the MDB file and the user name and password to use at login. Figure 8-7 shows the options you have when connecting to a SQL Server database using the Microsoft OLE DB Provider for SQL Server 6.5: here you must select the server name, enter login data, and select, if you want, a database name. (This is what ADO calls the initial catalog.) Remember that if you have a blank password, you must tick the Blank Password check box because simply leaving the password field empty won't work. You don't need to specify a user name and password if you rely on Windows NT integrated security. In that case, SQL Server uses the name and password provided at login time to check whether you're granted access to the server. Click on the Test Connection button to be sure that everything is OK.

Figure 8-7. The connection properties of the Microsoft OLE DB Provider for SQL Server.

When you use the default Microsoft OLE DB Provider for ODBC Drivers, the second page of the wizard is different. In this case, you can opt for a DSN or use a connection string (which broadly corresponds to a DSN-less connection). If you've chosen to use a connection string, you can build one starting with an existing DSN or create one from scratch, and you can also enter other properties in a dialog box whose content depends on the ODBC driver you're using. If you fill in the user name, password, and database name fields in this dialog box (on the right in Figure 8-8), you don't need to type these values again in the corresponding fields of the wizard page (on the left in the same figure).

Figure 8-8. Building a connection string.

You seldom need to enter any values in the remaining two pages of the Data Link wizard. But you can optimize your application's performance if you specify in the Advanced page that you're opening the database for read-only operations exclusively, and you can prevent timeout errors by setting a higher value for the Connection Timeout property. In the last wizard page, named All, you see a summary of all the connection properties. Some of these attributes are also present in the previous pages, while others can be modified only here. In general, these are advanced settings, and you shouldn't alter their default values unless you know what you're doing. Because each OLE DB provider exposes a different set of properties, you should refer to the documentation of the specific provider for additional information.

You can create a new database table—if the underlying provider allows you to do so—by right-clicking an existing table object and selecting the New Table command from the pop-up menu. You'll be asked for the name of the new table, and then a window like the one in Figure 8-9 will appear. You must enter the name of each field in the new table in this window, together with its type (integer, floating-point number, string, and so on), size, precision, and default value. You must also decide whether the field can accept Null values and whether the field is the identity key for the table.

Figure 8-9. When you create a new table or edit the design of an existing one, you can copy and paste field attributes from other tables.

The next step you take when creating a table is deciding which field (or fields) should be the primary key, which can include multiple columns, if necessary. A primary key is useful for uniquely identifying each record in the table and for enforcing relational integrity rules. You also generally need a primary key to create updatable cursors on the table. To create a primary key, select the row that contains the field key (use Ctrl+mouse click to select multiple columns), and then right-click on the grid and select the Set Primary Key menu command. You can create only one primary key per table, and the involved columns can't allow Null values. A key icon will appear on the left border for all the fields that are part of the primary key.

If you right-click on the grid and select the Properties menu command, the dialog box shown in Figure 8-10 appears. Here you can define the constraints, the relationships, and the indices for this table.

A constraint is a simple validation rule that involves one or more fields in the table, for example:

price > 0

A table can have multiple constraints, and each constraint has a name. You can have the database enforce the constraint for all the records already in the table, for all subsequent insert and delete operations, and when the database is being replicated.

In the second page of the Properties dialog box, you define the attributes for the relationships this table is involved in.

In the third page of the Properties dialog box, you create and delete the indices associated with the table. Indices are database entities that permit you to quickly retrieve the information in a table if you know the value of one or more fields. Indices also serve to create relationships between tables and to enforce referential integrity constraints. You can define several types of indices. A primary key index is the main index for a table; none of its fields can have a Null value and their combination must have a unique value so that each row of the table can be uniquely identified. A foreign key index is based on one or more keys that are primary keys in another table, and it's used when the two tables participate in a relationship. A unique index is based on any field (or combination of fields) that has a unique value in all the rows of the table. A unique index differs from a primary index in that a unique index can accept Null values. You can also have non-unique indices, which are often used to speed up searches without enforcing any constraints on the fields upon which they're based. Some database engines also support clustered indices, which ensure that the records in the table are physically arranged in the same order as they are in the index. (You can have only one index of this type in each table.)

Figure 8-10. You can create constraints in a table's Properties page.

When you complete the definition of a new table or when you alter the layout of an existing table, you can select the Save Change Script command from the File menu. This creates a script containing a sequence of SQL commands that reproduce the edits you've just completed. You can save this script to use as a reference or to enable you to re-create the table in other SQL Server systems. You can't decide where the script is saved, though. For example, on my system all scripts are stored in files with .sql extensions, which are saved in the C:\Program Files\Microsoft Visual Studio\Vb98 directory.

As I mentioned earlier, database diagrams display all or part of the tables in your database, including their fields, their keys, and the relationships among them. You can create diagrams for SQL Server and Oracle databases by right-clicking on a database Diagram folder in the DataView windows and selecting the New Diagram menu command. This command brings up the Database Diagram window, another Visual Database tool. You can drag-and-drop tables from the DataView window to the Database Diagram window, and when the diagram includes more tables the relationships among them are automatically displayed. You can also display all the tables that are related to a table already in the diagram by right-clicking on the table and selecting the Add Related Tables menu command. Figure 8-11 shows a diagram I created for the sample Pubs database that comes with SQL Server. Using the database diagram window, you can perform several interesting tasks:

• You can automatically arrange all tables, or just the selected tables, using the command in the Diagram menu.
• You can show or hide the names of the relationships using the Show Relationship Labels command in the Diagram menu or in the pop-up menu that appears when you right-click on the Database Diagram window background.
• You can add a text annotation using the New Text Annotation menu command, located in the Diagram menu or in the Database Diagram window pop-up menu. You can change the attributes of the text you enter using the Set Text Font command in the Diagram menu.
• You can select one or more tables in the diagram and change their appearance. You can decide to display just a table name, the key field names, all column names, or all column names and attributes, and you can even define a custom view to display only the column attributes you want.
• Because all the information in the tables is alive, you can change a table's layout without leaving the Database Diagram window, and you can also create and delete columns and select primary keys. (See the pop-up menu in Figure 8-11.) You can delete the table from the diagram or drop it from the database using the Delete Table from Database and Remove Table from Diagram menu commands respectively, so pay attention to which option you select. If you delete a table from the database, all the data it contains is gone forever.
• You can change the zoom factor of the diagram window, from the Zoom submenu in the Edit menu or from the pop-up menu that you display by right-clicking the window background.
• You can print the diagram using the Print command from the File menu. Before printing, you might want to recalculate and view page breaks using the corresponding commands in the Diagram menu or the pop-up menu. Unlike the Print Setup command in the File menu, the Print Setup command in the pop-up menu lets you select the zoom factor of the printed page.
• Finally, you can save the diagram to the database with the Save and Save As commands in the File menu.

The nature of relationships between tables is a key factor in the design of a database. You can place tables in one-to-one relationships or in one-to-many relationships. An example of a one-to-one relationship is the relationship between the Publishers and Pub_info tables in the Pubs database. An example of a one-to-many relationship is that between the Publishers and Titles tables. You can revise the attributes of a relationship in the Database Diagram window by right-clicking on the line that connects two related tables and selecting the Properties menu command.

The Database Diagram window can also work as a source for drag-and-drop operations. For example, you can copy a subset of the tables in a diagram to another diagram by selecting the subset and dragging the tables to the other diagram's window. Using drag-and-drop, you can also assemble multiple diagrams into a larger one. These techniques are particularly useful when you're dealing with databases with a lot of tables.

Figure 8-11. A database diagram for the Pubs database.

If you right-click on a Views folder inside the Data View window, you can create a new view. A view is either a subset of all the rows from a database table or the logical join of two or more tables in the database. For example, the Titleview view in the Pubs database shows all the titles in the database together with their authors. To retrieve this information, the view has to collect data from three distinct tables, Authors, Titles, and TitlesAuthor. When you create a new view, or edit an existing one, the Query Builder window appears. This is another member of the Visual Database Tools suite and is also one of the most useful because it lets you create queries and views using an intuitive user interface. The Query Builder window is subdivided into four panes, as you can see in Figure 8-12.

• In the Diagram pane, you arrange tables dropped from the DataView or the Database Diagram windows, from which you can select the fields that should appear in the query. You can display four different pop-up menus from this pane, which arise from a right-click on a table caption, a field name, a relationship connecting line, or the pane's background. Some of these commands are also included in the Query top-level menu, which becomes active when the Query Builder window is the foreground window.
• In the Grid pane, you can define the query or the view using a tabular format. For each field, you can define an alias name (that is, the name that field will appear under in the result set) and a criteria selection. For example, you can create a view that returns only the titles from a specific publisher or those whose price is below a given threshold.
• In the SQL pane, you see the query in SQL syntax. You can modify the query using the Diagram or the Grid panes and see how the SQL string changes, but you can also change the SQL query and watch the other two panes update their contents and appearance. (Some queries are too complex for Query Builder to display graphically, though.) You can also have Query Builder verify the syntax of the SQL string that you've typed, using the Verify SQL Syntax command in the Query menu.
• In the Result pane, you can see the result of the query or the view so that you have a chance to confirm that your query does exactly what you want. To populate this pane, you must select the Run command from the Query menu or from the pop-up menu that appears when you right-click anywhere in the Query Builder window. When you modify the query or view using one of the other three panes, the contents of this pane are grayed out to remind you that they aren't up to date.

You can hide and show individual panes using the Show Panes submenu in the View top-level menu. Commands in the Query menu let you add filters, sort or group the results based on a field value, and more. You can also use the Query Builder window to create SQL commands other than SELECT. You usually do this using the Change Type submenu from the Query menu, but these commands are disabled when you're creating or modifying a view (which can be based only on a SELECT command). For examples of other SQL commands, see "Crash Course in SQL" at the end of this chapter.

Figure 8-12. The Query Builder open in the Titleview view of the Pubs database.